/***********************************************************************************************************************************
 *    Name: RDCMan.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Generate the server list for Remote Desktop Connection Manager.
 *      HT: http://stackoverflow.com/a/17571853/2871586

	C:\Users\%USERNAME%\OneDrive\Documents\RDCMan
**/
USE DBAdmin;
IF OBJECT_ID(N'dbo.RDCMan_ServerGroups') IS NULL EXECUTE (N'CREATE FUNCTION dbo.RDCMan_ServerGroups (@parent_id INT) RETURNS XML BEGIN RETURN (SELECT CAST(Null AS XML)) END;')
GO

ALTER FUNCTION dbo.RDCMan_ServerGroups (@parent_id INT) RETURNS XML BEGIN RETURN (
  SELECT
	[properties/name]		= g.name,
	[properties/expanded]	= N'True',
	[properties/comment]	= g.[description],
	[properties/*]			= CAST(N'<logonCredentials inherit="FromParent"/><connectionSettings inherit="FromParent"/><gatewaySettings inherit="FromParent"/><remoteDesktop inherit="FromParent"/><localResources inherit="FromParent"/><securitySettings inherit="FromParent"/><displaySettings inherit="FromParent"/>' AS XML),
							  dbo.RDCMan_ServerGroups(g.server_group_id),
							  (SELECT
									name,
									displayName,
									--comment= '',
									CAST(N'<logonCredentials inherit="FromParent"/><connectionSettings inherit="FromParent"/><gatewaySettings inherit="FromParent"/><remoteDesktop inherit="FromParent"/><localResources inherit="FromParent"/><securitySettings inherit="FromParent"/><displaySettings inherit="FromParent"/>' AS XML)
								  FROM (SELECT DISTINCT server_group_id,
										name		= SUBSTRING(server_name, 1, LEN(server_name)-CHARINDEX(N'\',REVERSE(server_name))),
										displayName	= SUBSTRING(       name, 1, LEN(       name)-CHARINDEX(N'\',REVERSE(       name)))
									  FROM msdb.dbo.sysmanagement_shared_registered_servers) s WHERE s.server_group_id = g.server_group_id AND s.name NOT LIKE N'%(-)'
								  FOR XML PATH ('server'), TYPE)
  FROM msdb.dbo.sysmanagement_shared_server_groups g
  WHERE parent_id = @parent_id
  FOR XML PATH('group')
) END;
GO

DECLARE
  @groups	XML= dbo.RDCMan_ServerGroups(1),
  @rdg		XML= N'
<RDCMan schemaVersion="1">
  <version>2.2</version>
  <file>
	<properties>
	  <name>BBAAviation Worldwide SQL Servers</name>
	  <expanded>True</expanded>
	  <comment />
	  <logonCredentials inherit="FromParent" />
	  <connectionSettings inherit="FromParent" />
	  <gatewaySettings inherit="FromParent" />
	  <remoteDesktop inherit="FromParent" />
	  <localResources inherit="FromParent" />
	  <securitySettings inherit="FromParent" />
	  <displaySettings inherit="FromParent" />
	</properties>
	<server>
      <name>DC1-vp-DBAdmin.BBAAviation.net</name>
      <displayName>DC1-vp-DBAdmin</displayName>
      <logonCredentials inherit="FromParent" />
      <connectionSettings inherit="FromParent" />
      <gatewaySettings inherit="FromParent" />
      <remoteDesktop inherit="FromParent" />
      <localResources inherit="FromParent" />
      <securitySettings inherit="FromParent" />
      <displaySettings inherit="FromParent" />
	</server>
  </file>
</RDCMan>';
SET @rdg.modify('insert sql:variable("@groups") into (/RDCMan/file)[1]');
SELECT @rdg AS RDCMan;
GO